import pandas as pd
import re
import pymysql

df = pd.read_csv('SortPrice.csv',encoding = 'GBK')

print(df.keys())
db = pymysql.connect(user='root', password='', host='127.0.0.1', database='yyr')

count = 0
with db.cursor() as cursor:

    for column in df.values:
        column = list(column)[:-1]

        # 处理面积字符串*
        area = None
        if isinstance(column[3], str):
            area = max([int(area) for area in re.findall(r'\d+', column[3])])

        column[3] = area

        # 处理房屋特点字符串
        # 特点 弄成字符串，用逗号","分隔
        column[4] = sorted(re.findall(r"'(.*?)'", column[4]))
        column[4] = ','.join(column[4])

        # 处理价格字符串
        # 存在 “价格待定”
        try:
            column[5] = int(column[5])

        except ValueError:
            column[5] = None
        sql = f"""INSERT INTO clear(id, name, type, address, area, tags, price)
         VALUES ({count + 1}, '{column[0]}', '{column[1]}', '{column[2]}',
          {"'" + str(column[3]) + "'" if column[3] else "NULL"},
         {"'" + str(column[4]) + "'" if column[4] else 'NULL'}, 
         {column[5] if column[5] else 'NULL'})"""
        cursor.execute(sql)
        count += 1
    db.commit()



